package com.main.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import com.main.ui.model.Person;

@Repository
public class PersonJDBCDao {

	private  Logger logger = LoggerFactory.getLogger(PersonJDBCDao.class);
	
	public Connection openConnection() {
		Connection c = null;
		try {
			Class.forName("org.sqlite.JDBC");
			c = DriverManager.getConnection("jdbc:sqlite:D:\\Userfiles\\tbal\\workspace\\person-inventory\\personInventory.sqlite");
		} catch (Exception e) {
			logger.error(e.getClass().getName() + ": " + e.getMessage());
		}
		logger.info("Opened database successfully");
		return c;
	}
	
	public Person getPerson(Long personId) {
		Connection c = null;
		Statement stmt = null;
		ResultSet rs = null;
		Person person = null;
		try {
			c = openConnection();
			stmt = c.createStatement();
			rs = stmt.executeQuery("select * from users where id=" + personId + ";");
			if (rs != null) {
				person = new Person();
				person.setId(rs.getLong("id"));
				person.setFirstName(rs.getString("first_name"));
				person.setAge(rs.getInt("age"));
				person.setLastName(rs.getString("last_name"));
				person.setGender(rs.getString("gender"));
			}
			rs.close();
			stmt.close();
			c.close();
			logger.info("Person fetched through JDBC call.");
		}  catch (Exception e) {
			logger.error(e.getClass().getName() + ": " + e.getMessage());
		}
		return person;
	}
	
	public List<Person> getAllPersons() {
		Connection c = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Person> personList = null;
		try {
			c = openConnection();
			stmt = c.createStatement();
			rs = stmt.executeQuery("select * from users;");
			personList = new ArrayList<Person>();
			while (rs.next()) {
				Person person = new Person();
				person.setId(rs.getLong("id"));
				person.setFirstName(rs.getString("first_name"));
				person.setAge(rs.getInt("age"));
				person.setLastName(rs.getString("last_name"));
				person.setGender(rs.getString("gender"));
				personList.add(person);
			}
			rs.close();
			stmt.close();
			c.close();
			logger.info("No of Persons fetched through JDBC call: " + String.valueOf(personList.size()));
		} catch (Exception e) {
			logger.error(e.getClass().getName() + ": " + e.getMessage());
		}
		return personList;
	}
	
	public boolean insertPerson(Person person) {
		Connection c = null;
		Statement stmt = null;
		try {
			c = openConnection();
			c.setAutoCommit(false);
			stmt = c.createStatement();
			String sql = "INSERT INTO USERS (age, first_name, gender, last_name)"
					+ "VALUES ("
					+ person.getAge()
					+ ","
					+ "'"
					+ person.getFirstName()
					+ "'"
					+ ","
					+ "'"
					+ person.getGender()
					+ "'"
					+ ","
					+ "'"
					+ person.getLastName() + "'" + ")";
			stmt.executeUpdate(sql);
			stmt.close();
			c.commit();
			c.close();
			logger.info("Record created successfully");
			return true;
		} catch (Exception e) {
			logger.error(e.getClass().getName() + ": " + e.getMessage());
		}
		return false;
	}
	
	public boolean updatePerson(Person person) {
		Connection c = null;
		Statement stmt = null;
		try {
			c = openConnection();
			c.setAutoCommit(false);
			stmt = c.createStatement();
			String sql = "UPDATE USERS SET age=" + person.getAge()
					+ ",first_name=" + "'" + person.getFirstName() + "'"
					+ ",last_name=" + "'" + person.getLastName() + "'"
					+ ",gender=" + "'" + person.getGender() + "'"
					+ " WHERE id=" + person.getId() + ";";
			
			stmt.executeUpdate(sql);
			stmt.close();
			c.commit();
			c.close();
			logger.info("Record updated successfully");
			return true;
		} catch (Exception e) {
			logger.error(e.getClass().getName() + ": " + e.getMessage());
		}
		return false;
	}
	
	public boolean deletePerson(Long personId) {
		Connection c = null;
		Statement stmt = null;
		try {
			c = openConnection();
			c.setAutoCommit(false);
			stmt = c.createStatement();
			String sql = "DELETE FROM USERS WHERE id="+personId+";";
			stmt.executeUpdate(sql);
			stmt.close();
			c.commit();
			c.close();
			logger.info("Record deleted successfully");
			return true;
		} catch (Exception e) {
			logger.error(e.getClass().getName() + ": " + e.getMessage());
		}
		return false;
	}
}
